package org.demo.db.meta.impl;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.demo.db.meta.Constraint;
import org.demo.db.meta.DbMeta;
import org.demo.db.meta.Field;
import org.demo.db.meta.Index;
import org.demo.db.meta.Table;

/**
 * 普通数据库 -> 元数据信息
 * @author  
 * @date    2010-9-3
 * @file    org.demo.db.meta.impl.CommonDbMetaImpl.java
 */
public class CommonDbMetaImpl implements DbMeta{
	protected Connection conn;
	protected DatabaseMetaData dbmd;
	protected String catalog = null;
	/**
	 * 构造函数
	 * @param driver
	 * @param url
	 * @param user
	 * @param password
	 * @throws Exception
	 */
	public CommonDbMetaImpl(
		String driver,String url,
		String user,String password)throws Exception{
		Class.forName(driver);
		conn = DriverManager.getConnection(url, user, password);
		dbmd = conn.getMetaData();
		catalog = conn.getCatalog();
	}
	/**
	 * 构造函数
	 * @param conn
	 */
	public CommonDbMetaImpl(Connection conn)throws SQLException{
		if (conn == null){
			throw new RuntimeException("conn can't be null.");
		}
		this.conn = conn;
		dbmd = conn.getMetaData();
		catalog = conn.getCatalog();
	}
	/**
	 * 获取当前用户名称
	 * @return
	 */
	public String getCurrentUser()throws SQLException {
		return dbmd.getUserName();
	}
	/**
	 * 获取数据库中所有 schema
	 * @return
	 */
	public List<String> getSchemas()throws SQLException{
		List<String> schemas = new ArrayList<String>();
		ResultSet rs = dbmd.getSchemas();
		while(rs.next()){
			String schema = rs.getString("TABLE_SCHEM");
			schemas.add(schema);
		}
		// 关闭游标
		rs.close();
		return schemas;
	}
	/**
	 * 获取当前链接的 schema
	 * @return
	 * @throws SQLException
	 */
	public String getCurrentSchema()throws SQLException{
		String userName = dbmd.getUserName();
		
		List<String> schemas = getSchemas();
		return (schemas.size() > 0) ? userName.toUpperCase() : null;
	}
	/**
	 * 获取表信息
	 * @return
	 * @throws SQLException
	 */
	public List<Table> getTables()throws SQLException{
		String schema = getCurrentSchema();
		List<Table> tables = new ArrayList<Table>();
		
		ResultSet rs = dbmd.getTables(catalog, schema, null, new String[]{"TABLE"});
		while(rs.next()){
			String tableSchema = rs.getString("TABLE_SCHEM");
			String tableName = rs.getString("TABLE_NAME");
			String remarks = rs.getString("REMARKS");
			String tableType = rs.getString("TABLE_TYPE");
			List<Field> fields = getFields(schema, tableName);
			List<Constraint> pks = getPrimaryKeys(schema, tableName);
			List<Constraint> fks = getForeignKeys(schema, tableName);
			List<Index> indexes = getIndexes(schema, tableName);
			
			Table table = new Table();
			table.setSchema(tableSchema);
			table.setName(tableName);
			table.setRemarks(remarks);
			table.setTableType(tableType);
			table.setFields(fields);
			table.setPrimaryKeys(pks);
			table.setForeignKeys(fks);
			table.setIndexes(indexes);
			tables.add(table);
		}
		// 关闭游标
		rs.close();
		return tables;
	}
	/**
	 * 获取列信息
	 * @param schema
	 * @param table
	 * @return
	 * @throws SQLException
	 */
	public List<Field> getFields(
			String schema,String table)throws SQLException{
		List<Field> fields = new ArrayList<Field>();
		ResultSet rs = dbmd.getColumns(catalog, schema, table, null);
		while(rs.next()){
			String name = rs.getString("COLUMN_NAME");
			String remarks = rs.getString("REMARKS");
			String type = rs.getString("TYPE_NAME");
			int length = rs.getInt("COLUMN_SIZE");
			int precision = rs.getInt("DECIMAL_DIGITS");
			boolean nullable = rs.getBoolean("NULLABLE");
			String defaultValue = rs.getString("COLUMN_DEF");
			
			Field field = new Field();
			field.setName(name);
			field.setRemarks(remarks);
			field.setType(type);
			field.setLength(length);
			field.setPrecision(precision);
			field.setNullable(nullable);
			field.setDefaultValue(defaultValue);
			
			fields.add(field);
		}
		// 关闭游标
		rs.close();
		return fields;
	}
	/**
	 * 获取主键信息
	 * @param schema
	 * @param table
	 * @return
	 * @throws SQLException
	 */
	public List<Constraint> getPrimaryKeys(
			String schema,String table)throws SQLException{
		List<Constraint> list = new ArrayList<Constraint>();
		ResultSet rs = dbmd.getPrimaryKeys(catalog, schema, table);
		while(rs.next()){
			String name = rs.getString("PK_NAME");
			String columnName = rs.getString("COLUMN_NAME");
			
			Constraint cons = new Constraint();
			cons.setName(name);
			cons.setType("Primary Key");
			cons.setSchema(schema);
			cons.setTableName(table);
			cons.setColumnName(columnName);
			
			list.add(cons);
		}
		// 关闭游标
		rs.close();
		return list;
	}
	/**
	 * 获取外键信息
	 * @param schema
	 * @param table
	 * @return
	 * @throws SQLException
	 */
	public List<Constraint> getForeignKeys(
			String schema,String table)throws SQLException{
		List<Constraint> list = new ArrayList<Constraint>();
		ResultSet rs = dbmd.getImportedKeys(catalog, schema, table);
		while(rs.next()){
			String name = rs.getString("FK_NAME");
			String columnName = rs.getString("FKCOLUMN_NAME");
			String pkSchema = rs.getString("PKTABLE_SCHEM");
			String pkTable = rs.getString("PKTABLE_NAME");
			String pkColumn = rs.getString("PKCOLUMN_NAME");
			short delRule = rs.getShort("DELETE_RULE");
			
			Constraint cons = new Constraint();
			cons.setName(name);
			cons.setType("Foreign Key");
			cons.setSchema(schema);
			cons.setTableName(table);
			cons.setColumnName(columnName);
			cons.setRefSchema(pkSchema);
			cons.setRefTable(pkTable);
			cons.setRefColumnName(pkColumn);
			cons.setDelRule(getRuleAsString(delRule));
			
			list.add(cons);
		}
		// 关闭游标
		rs.close();
		return list;
	}
	/**
	 * 获取索引信息
	 * @param schema
	 * @param table
	 * @return
	 * @throws SQLException
	 */
	public List<Index> getIndexes(
			String schema,String table)throws SQLException{
		List<Index> list = new ArrayList<Index>();
		ResultSet rs = dbmd.getIndexInfo(catalog, schema, table, false, false);
		while(rs.next()){
			String name = rs.getString("INDEX_NAME");
			short type = rs.getShort("TYPE");
			String tableSchema = rs.getString("TABLE_SCHEM");
			String tableName = rs.getString("TABLE_NAME");
			String columnName = rs.getString("COLUMN_NAME");
			short position = rs.getShort("ORDINAL_POSITION");
			boolean nonUnique = rs.getBoolean("NON_UNIQUE");
			// 当 type == "Statistic" 时, name == null, 此处剔除静态索引
			if (name == null || columnName == null){
				continue;
			}
			// 
			Index index = new Index();
			index.setName(name);
			index.setType(getIndexTypeAsString(type));
			index.setTableSchema(tableSchema);
			index.setTableName(tableName);
			index.setColumnName(columnName);
			index.setPosition(position);
			index.setUnique(!nonUnique);
			
			list.add(index);
		}
		// 关闭游标
		rs.close();
		return list;
	}
	/**
	 * 关闭连接
	 */
	public void close() {
		try {
			if (conn != null){
				conn.close(); 
				conn = null;
			}
		}catch(Exception e){
		}
	}
	/**
	 * 获取规则的字符串名称
	 * @param rule
	 * @return
	 */
	protected String getRuleAsString(short rule){
		String name = "";
		if (rule == DatabaseMetaData.importedKeyNoAction){
			name = "NoAction";
		} else if (rule == DatabaseMetaData.importedKeyCascade){
			name = "Cascade";
		} else if (rule == DatabaseMetaData.importedKeySetNull){
			name = "SetNull";
		} else if (rule == DatabaseMetaData.importedKeyRestrict){
			name = "Restrict";
		} else if (rule == DatabaseMetaData.importedKeySetDefault){
			name = "SetDefault";
		}
		return name;
	}
	/**
	 * 获取索引类型的字符串名称
	 * @param type
	 * @return
	 */
	protected String getIndexTypeAsString(short type){
		String name = "";
		if (type == DatabaseMetaData.tableIndexStatistic){
			name = "Statistic";
		} else if (type == DatabaseMetaData.tableIndexClustered){
			name = "Clustered";
		} else if (type == DatabaseMetaData.tableIndexHashed){
			name = "Hashed";
		} else if (type == DatabaseMetaData.tableIndexOther){
			name = "Other";
		}
		return name;
	}	
}
